package com.iengoo.inductionms.common;

import com.iengoo.inductionms.entity.Employee;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public class EduExcel {
    public static void export(String path, String name, List<Employee> employees) throws IOException {
        //定义一个新的工作簿
        Workbook wb = new HSSFWorkbook();
        // 创建第一个Sheet页
        Sheet sheet = wb.createSheet();
        sheet.setDefaultColumnWidth(20);//自定义设置列宽和行高
        sheet.setDefaultRowHeightInPoints(14);
        //创建一个行
        Row row = sheet.createRow(0);

        Font font = wb.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 10);//设置字体大小
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
        /**
         * 单元格背景为黄色
         */
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());//设置背景色
        cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

        cellStyle.setAlignment(CellStyle.ALIGN_CENTER );//居中
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);//下边框
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);//左边框
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);//上边框
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);//右边框
        cellStyle.setFont(font);
        /**
         * 单元格背景为白色
         */
        CellStyle cellStyle1 = wb.createCellStyle();
        cellStyle1.setAlignment(CellStyle.ALIGN_CENTER );
        cellStyle1.setBorderBottom(CellStyle.BORDER_THIN);//下边框
        cellStyle1.setBorderLeft(CellStyle.BORDER_THIN);//左边框
        cellStyle1.setBorderRight(CellStyle.BORDER_THIN);//上边框
        cellStyle1.setBorderTop(CellStyle.BORDER_THIN);//右边框
        cellStyle1.setFont(font);

        //创建一个单元格  第1列
        Cell cell = row.createCell(0);
        cell.setCellValue("员工编号");
        cell.setCellStyle(cellStyle);


        Cell cell1 = row.createCell(1);
        cell1.setCellValue("姓名");
        cell1.setCellStyle(cellStyle);


        Cell cell2 = row.createCell(2);
        cell2.setCellValue("入学时间");
        cell2.setCellStyle(cellStyle);

        Cell cell3 = row.createCell(3);
        cell3.setCellValue("毕肄业时间");
        cell3.setCellStyle(cellStyle);

        Cell cell4 = row.createCell(4);
        cell4.setCellValue("毕业学校");
        cell4.setCellStyle(cellStyle);

        Cell cell5 = row.createCell(5);
        cell5.setCellValue("学历");
        cell5.setCellStyle(cellStyle);

        Cell cell6 = row.createCell(6);
        cell6.setCellValue("毕业情况");
        cell6.setCellStyle(cellStyle);

        Cell cell7 = row.createCell(7);
        cell7.setCellValue("最高学历标识");
        cell7.setCellStyle(cellStyle);

        Cell cell8 = row.createCell(8);
        cell8.setCellValue("参加工作时学历标识");
        cell8.setCellStyle(cellStyle);

        Cell cell9 = row.createCell(9);
        cell9.setCellValue("学位类别");
        cell9.setCellStyle(cellStyle1);

        Cell cell10 = row.createCell(10);
        cell10.setCellValue("学位");
        cell10.setCellStyle(cellStyle1);

        Cell cell11 = row.createCell(11);
        cell11.setCellValue("最高学位标识");
        cell11.setCellStyle(cellStyle);

        Cell cell12 = row.createCell(12);
        cell12.setCellValue("学位授予时间");
        cell12.setCellStyle(cellStyle1);

        Cell cell13 = row.createCell(13);
        cell13.setCellValue("学制");
        cell13.setCellStyle(cellStyle1);

        Cell cell14 = row.createCell(14);
        cell14.setCellValue("所学专业");
        cell14.setCellStyle(cellStyle1);

        Cell cell15 = row.createCell(15);
        cell15.setCellValue("学习形式");
        cell15.setCellStyle(cellStyle1);

        Cell cell16 = row.createCell(16);
        cell16.setCellValue("所学专业类别");
        cell16.setCellStyle(cellStyle1);

        Cell cell17 = row.createCell(17);
        cell17.setCellValue("是否全日制");
        cell17.setCellStyle(cellStyle);

        createRows(sheet, employees);

        FileOutputStream fileOut = new FileOutputStream(path + name + ".xls");
        wb.write(fileOut);
        fileOut.close();
    }

    public static void createRows(Sheet sheet, List<Employee> employees) {
        for (int i= 0; i < employees.size(); i++) {
            Row row = sheet.createRow(i + 1);
            Employee e = employees.get(i);

            //创建一个单元格  第1列
            Cell cell = row.createCell(0);
            cell.setCellValue(nullToBlank(e.getEmployeeId()));

            Cell cell1 = row.createCell(1);
            cell1.setCellValue(nullToBlank(e.getEmployeeName()));

            Cell cell2 = row.createCell(2);
            cell2.setCellValue(format(e.getAdmissionTime()));

            Cell cell3 = row.createCell(3);
            cell3.setCellValue(format(e.getGraduationTime()));

            Cell cell4 = row.createCell(4);
            cell4.setCellValue(nullToBlank(e.getGraduationSchool()));

            Cell cell5 = row.createCell(5);
            cell5.setCellValue(nullToBlank(e.getEducation()));

            Cell cell6 = row.createCell(6);
            cell6.setCellValue(nullToBlank(e.getGraduationSituation()));

            Cell cell7 = row.createCell(7);
            cell7.setCellValue(nullToBlank(e.getHighestDegree()));

            Cell cell8 = row.createCell(8);
            cell8.setCellValue(nullToBlank(e.getWorkEducation()));

            Cell cell9 = row.createCell(9);
            cell9.setCellValue(nullToBlank(e.getDegreeCategory()));

            Cell cell10 = row.createCell(10);
            cell10.setCellValue(nullToBlank(e.getAcademicDegree()));

            Cell cell11 = row.createCell(11);
            cell11.setCellValue(nullToBlank(e.getHighestDegree()));

            Cell cell12 = row.createCell(12);
            cell12.setCellValue(format(e.getDegreeAwardTime()));

            Cell cell13 = row.createCell(13);
            cell13.setCellValue(nullToBlank(e.getEducationalSystem()));

            Cell cell14 = row.createCell(14);
            cell14.setCellValue(nullToBlank(e.getProfession()));

            Cell cell15 = row.createCell(15);
            cell15.setCellValue(nullToBlank(e.getLearningModality()));

            Cell cell16 = row.createCell(16);
            cell16.setCellValue(nullToBlank(e.getProfessionalClassification()));

            Cell cell17 = row.createCell(17);
            cell17.setCellValue(nullToBlank(e.getLearningModality()));
        }

    }

    /**
     * 空对象转换过为 ""空字符串
     * @param o
     * @return
     */
    public static String nullToBlank(String o) {
        return o == null ? "" : o;
    }

    public static String format(Date date) {
        return date == null ? "" : new SimpleDateFormat("yyyy-MM-dd").format(date);
    }

}
